using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using WinLOAtest.Models;

namespace WinLOAtest.Data
{
    /// <summary>
    /// 数据库操作帮助类
    /// </summary>
    public class DatabaseHelper
    {
        //private static string connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"]?.ConnectionString
        //    ?? @"Data Source=ESLXG\SQL2019;Initial Catalog=UcaesHn;User ID=sa;Password=11112222;";
        private static string connectionString = @"Data Source=ESLXG\SQL2019;Initial Catalog=UcaesHn;User ID=sa;Password=11112222;";

        /// <summary>
        /// 初始化数据库表结构
        /// </summary>
        public static void InitializeDatabase()
        {
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // 创建账号表
                string createAccountTable = @"
                    IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='Accounts' AND xtype='U')
                    CREATE TABLE Accounts (
                        Id int IDENTITY(1,1) PRIMARY KEY,
                        AccountName nvarchar(50) NOT NULL UNIQUE,
                        FeatureString nvarchar(20) NOT NULL DEFAULT '00000000000000000000',
                        CreateTime datetime NOT NULL DEFAULT GETDATE(),
                        UpdateTime datetime NOT NULL DEFAULT GETDATE(),
                        Remark nvarchar(200)
                    )";

                // 创建功能配置表
                string createFeatureTable = @"
                    IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='FeatureConfigs' AND xtype='U')
                    CREATE TABLE FeatureConfigs (
                        FeatureId int PRIMARY KEY,
                        FeatureName nvarchar(50) NOT NULL,
                        Description nvarchar(200),
                        IsEnabled bit NOT NULL DEFAULT 1,
                        SortOrder int NOT NULL
                    )";

                using (var command = new SqlCommand(createAccountTable, connection))
                {
                    command.ExecuteNonQuery();
                }

                using (var command = new SqlCommand(createFeatureTable, connection))
                {
                    command.ExecuteNonQuery();
                }

                // 初始化功能配置数据
                InitializeFeatureConfigs(connection);
            }
        }

        /// <summary>
        /// 初始化功能配置数据
        /// </summary>
        private static void InitializeFeatureConfigs(SqlConnection connection)
        {
            string checkData = "SELECT COUNT(*) FROM FeatureConfigs";
            using (var command = new SqlCommand(checkData, connection))
            {
                int count = (int)command.ExecuteScalar();
                if (count == 0)
                {
                    // 插入默认的20个功能配置
                    for (int i = 1; i <= 20; i++)
                    {
                        string insertFeature = @"
                            INSERT INTO FeatureConfigs (FeatureId, FeatureName, Description, IsEnabled, SortOrder)
                            VALUES (@FeatureId, @FeatureName, @Description, @IsEnabled, @SortOrder)";

                        using (var cmd = new SqlCommand(insertFeature, connection))
                        {
                            cmd.Parameters.AddWithValue("@FeatureId", i);
                            cmd.Parameters.AddWithValue("@FeatureName", $"功能{i}");
                            cmd.Parameters.AddWithValue("@Description", $"按钮{i}是否可用");
                            cmd.Parameters.AddWithValue("@IsEnabled", true);
                            cmd.Parameters.AddWithValue("@SortOrder", i);
                            cmd.ExecuteNonQuery();
                        }
                    }
                }
            }
        }

        #region 账号操作

        /// <summary>
        /// 添加账号
        /// </summary>
        public static bool AddAccount(Account account)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                string sql = @"
                    INSERT INTO Accounts (AccountName, FeatureString, CreateTime, UpdateTime, Remark)
                    VALUES (@AccountName, @FeatureString, @CreateTime, @UpdateTime, @Remark)";

                using (var command = new SqlCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@AccountName", account.AccountName);
                    command.Parameters.AddWithValue("@FeatureString", account.FeatureString);
                    command.Parameters.AddWithValue("@CreateTime", account.CreateTime);
                    command.Parameters.AddWithValue("@UpdateTime", account.UpdateTime);
                    command.Parameters.AddWithValue("@Remark", account.Remark ?? "");

                    return command.ExecuteNonQuery() > 0;
                }
            }
        }

        /// <summary>
        /// 更新账号
        /// </summary>
        public static bool UpdateAccount(Account account)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                string sql = @"
                    UPDATE Accounts 
                    SET AccountName = @AccountName, 
                        FeatureString = @FeatureString, 
                        UpdateTime = @UpdateTime, 
                        Remark = @Remark
                    WHERE Id = @Id";

                using (var command = new SqlCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@Id", account.Id);
                    command.Parameters.AddWithValue("@AccountName", account.AccountName);
                    command.Parameters.AddWithValue("@FeatureString", account.FeatureString);
                    command.Parameters.AddWithValue("@UpdateTime", DateTime.Now);
                    command.Parameters.AddWithValue("@Remark", account.Remark ?? "");

                    return command.ExecuteNonQuery() > 0;
                }
            }
        }

        /// <summary>
        /// 删除账号
        /// </summary>
        public static bool DeleteAccount(int accountId)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                string sql = "DELETE FROM Accounts WHERE Id = @Id";

                using (var command = new SqlCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@Id", accountId);
                    return command.ExecuteNonQuery() > 0;
                }
            }
        }

        /// <summary>
        /// 获取所有账号
        /// </summary>
        public static List<Account> GetAllAccounts()
        {
            var accounts = new List<Account>();
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                string sql = "SELECT Id, AccountName, FeatureString, CreateTime, UpdateTime, Remark FROM Accounts ORDER BY CreateTime DESC";

                using (var command = new SqlCommand(sql, connection))
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        accounts.Add(new Account
                        {
                            Id = reader.GetInt32(0),  // Id 列索引 0
                            AccountName = reader.GetString(1),  // AccountName 列索引 1
                            FeatureString = reader.GetString(2),  // FeatureString 列索引 2
                            CreateTime = reader.GetDateTime(3),  // CreateTime 列索引 3
                            UpdateTime = reader.GetDateTime(4),  // UpdateTime 列索引 4
                            Remark = reader.IsDBNull(5) ? "" : reader.GetString(5)  // Remark 列索引 5
                        });
                    }
                }
            }
            return accounts;
        }

        /// <summary>
        /// 根据账号名查询账号
        /// </summary>
        public static Account GetAccountByName(string accountName)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                string sql = "SELECT Id, AccountName, FeatureString, CreateTime, UpdateTime, Remark FROM Accounts WHERE AccountName = @AccountName";

                using (var command = new SqlCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@AccountName", accountName);
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            return new Account
                            {
                                Id = reader.GetInt32(0),
                                AccountName = reader.GetString(1),
                                FeatureString = reader.GetString(2),
                                CreateTime = reader.GetDateTime(3),
                                UpdateTime = reader.GetDateTime(4),
                                Remark = reader.IsDBNull(5) ? "" : reader.GetString(5)
                            };
                        }
                    }
                }
            }
            return null;
        }

        /// <summary>
        /// 根据功能ID查询拥有该功能的账号
        /// </summary>
        public static List<Account> GetAccountsByFeature(int featureId)
        {
            var accounts = new List<Account>();
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                string sql = @"
                    SELECT Id, AccountName, FeatureString, CreateTime, UpdateTime, Remark FROM Accounts 
                    WHERE SUBSTRING(FeatureString, @FeatureId, 1) = '1'
                    ORDER BY CreateTime DESC";

                using (var command = new SqlCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@FeatureId", featureId);
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            accounts.Add(new Account
                            {
                                Id = reader.GetInt32(0),
                                AccountName = reader.GetString(1),
                                FeatureString = reader.GetString(2),
                                CreateTime = reader.GetDateTime(3),
                                UpdateTime = reader.GetDateTime(4),
                                Remark = reader.IsDBNull(5) ? "" : reader.GetString(5)
                            });
                        }
                    }
                }
            }
            return accounts;
        }

        #endregion

        #region 功能配置操作

        /// <summary>
        /// 获取所有功能配置
        /// </summary>
        public static List<FeatureConfig> GetAllFeatureConfigs()
        {
            var features = new List<FeatureConfig>();
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                string sql = "SELECT FeatureId, FeatureName, Description, IsEnabled, SortOrder FROM FeatureConfigs ORDER BY SortOrder";

                using (var command = new SqlCommand(sql, connection))
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        features.Add(new FeatureConfig
                        {
                            FeatureId = reader.GetInt32(0),  // FeatureId 列索引 0
                            FeatureName = reader.GetString(1),  // FeatureName 列索引 1
                            Description = reader.IsDBNull(2) ? "" : reader.GetString(2),  // Description 列索引 2
                            IsEnabled = reader.GetBoolean(3),  // IsEnabled 列索引 3
                            SortOrder = reader.GetInt32(4)  // SortOrder 列索引 4
                        });
                    }
                }
            }
            return features;
        }

        /// <summary>
        /// 更新功能配置
        /// </summary>
        public static bool UpdateFeatureConfig(FeatureConfig feature)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                string sql = @"
                    UPDATE FeatureConfigs 
                    SET FeatureName = @FeatureName, 
                        Description = @Description, 
                        IsEnabled = @IsEnabled, 
                        SortOrder = @SortOrder
                    WHERE FeatureId = @FeatureId";

                using (var command = new SqlCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@FeatureId", feature.FeatureId);
                    command.Parameters.AddWithValue("@FeatureName", feature.FeatureName);
                    command.Parameters.AddWithValue("@Description", feature.Description ?? "");
                    command.Parameters.AddWithValue("@IsEnabled", feature.IsEnabled);
                    command.Parameters.AddWithValue("@SortOrder", feature.SortOrder);

                    return command.ExecuteNonQuery() > 0;
                }
            }
        }

        #endregion
    }
}